﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Web.Admin.Report
{
    public partial class RWBStorageToSell : System.Web.UI.Page
    {
   
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Qdtstart.Value = DateTime.Now.AddMonths(-1).ToShortDateString();
                Qdtend.Value = DateTime.Now.AddDays(1).ToShortDateString();
            }
        }
        private void DataBind()
        {


            //获取存粮信息
            DataTable dt = GetWBReport();
            Repeater1.DataSource = dt;
            Repeater1.DataBind();


            DataTable dt2 = GetTotalReport();
            Repeater2.DataSource = dt2;
            Repeater2.DataBind();
            //合计数据


        }

        private DataTable GetTotalReport()
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT E.strName AS VarietyID,C.Price_ShiChang,");
            strSql.Append("  SUM(VarietyCount) AS VarietyCount, SUM(VarietyInterest)AS VarietyInterest ,SUM(StorageMoney) AS StorageMoney,SUM(VarietyMoney) AS VarietyMoney");
            strSql.Append("   FROM dbo.StorageSell A INNER JOIN dbo.WB B ON A.WBID =B.ID");
            strSql.Append("  INNER JOIN dbo.Depositor H ON H.AccountNumber=A.Dep_AccountNumber");
            strSql.Append("  INNER JOIN dbo.WB F ON F.ID=H.WBID");
            strSql.Append("  INNER JOIN dbo.Dep_StorageInfo C ON A.Dep_SID=C.ID");
            strSql.Append("  INNER JOIN dbo.StorageTime D ON C.TimeID=D.ID");
            strSql.Append("  INNER JOIN dbo.StorageVariety E ON A.VarietyID=E.ID");
            strSql.Append("  where 1=1 and B.ISSimulate=0");
            if (ReportType.Value == "P")
            {
                strSql.Append("  and A.ISReturn=0");
            }
            else
            {
                strSql.Append("  and A.ISReturn!=0");
            }
            if (TradeWBID.Value == "1")
            {
                strSql.Append("   AND A.WBID=H.WBID");
            }
            else if (TradeWBID.Value == "2")
            {
                strSql.Append("   AND A.WBID!=H.WBID");
            }
            if (Request.Form["QWBID"] != null && Request.Form["QWBID"].ToString() != "")
            {
                strSql.Append("   AND B.ID = " + Request.Form["QWBID"].ToString());
            }
            if (Qdtstart.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Sell> '" + Qdtstart.Value.Trim() + "'");
            }
            if (Qdtend.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Sell < '" + Qdtend.Value.Trim() + "'");
            }

            strSql.Append("   GROUP BY E.strName,C.Price_ShiChang");
            strSql.Append("  ");


            DataTable dt2 = SQLHelper.ExecuteDataTable(strSql.ToString());
            return dt2;
        }

        private DataTable GetWBReport()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT F.ID as WBID,F.strName as WBName, B.ID as TradeWBID, B.strName AS TradeWBName,D.ID as TimeID, D.strName AS TimeName,E.ID as VarietyID, E.strName AS VarietyName,C.Price_ShiChang,");
            strSql.Append("  SUM(VarietyCount) AS VarietyCount, SUM(VarietyInterest)AS VarietyInterest ,SUM(StorageMoney) AS StorageMoney,SUM(VarietyMoney) AS VarietyMoney");
            strSql.Append("   FROM dbo.StorageSell A INNER JOIN dbo.WB B ON A.WBID =B.ID");
            strSql.Append("  INNER JOIN dbo.Depositor H ON H.AccountNumber=A.Dep_AccountNumber");
            strSql.Append("  INNER JOIN dbo.WB F ON F.ID=H.WBID");
            strSql.Append("  INNER JOIN dbo.Dep_StorageInfo C ON A.Dep_SID=C.ID");
            strSql.Append("  INNER JOIN dbo.StorageTime D ON C.TimeID=D.ID");
            strSql.Append("  INNER JOIN dbo.StorageVariety E ON A.VarietyID=E.ID");
            strSql.Append("  ");
            strSql.Append("  where 1=1 and B.ISSimulate=0");
            if (ReportType.Value == "P")
            {
                strSql.Append("  and A.ISReturn=0");
            }
            else
            {
                strSql.Append("  and A.ISReturn!=0");
            }

            if (TradeWBID.Value == "1")
            {
                strSql.Append("   AND A.WBID=H.WBID");
            }
            else if (TradeWBID.Value == "2")
            {
                strSql.Append("   AND A.WBID!=H.WBID");
            }

            if (Request.Form["QWBID"] != null && Request.Form["QWBID"].ToString() != "")
            {
                strSql.Append("   AND B.ID = " + Request.Form["QWBID"].ToString());
            }
            if (Qdtstart.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Sell> '" + Qdtstart.Value.Trim() + "'");
            }
            if (Qdtend.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Sell < '" + Qdtend.Value.Trim() + "'");
            }

            strSql.Append("    GROUP BY F.ID,F.strName,B.ID, B.strName,D.ID,D.strName,E.ID, E.strName,C.Price_ShiChang");
            strSql.Append("  ");


            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
            return dt;
        }

        protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
        {

            DataBind();

        }

        DataTable GetoutputTable()
        {
            //获取存粮信息
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT F.strName as WBName,  B.strName AS TradeWBName, D.strName AS TimeName, E.strName AS VarietyName,C.Price_ShiChang,");
            strSql.Append("  SUM(VarietyCount) AS VarietyCount, SUM(VarietyInterest)AS VarietyInterest ,SUM(StorageMoney) AS StorageMoney,SUM(VarietyMoney) AS VarietyMoney");
            strSql.Append("   FROM dbo.StorageSell A INNER JOIN dbo.WB B ON A.WBID =B.ID");
            strSql.Append("  INNER JOIN dbo.Depositor H ON H.AccountNumber=A.Dep_AccountNumber");
            strSql.Append("  INNER JOIN dbo.WB F ON F.ID=H.WBID");
            strSql.Append("  INNER JOIN dbo.Dep_StorageInfo C ON A.Dep_SID=C.ID");
            strSql.Append("  INNER JOIN dbo.StorageTime D ON C.TimeID=D.ID");
            strSql.Append("  INNER JOIN dbo.StorageVariety E ON A.VarietyID=E.ID");
            strSql.Append("  ");
            strSql.Append("  where 1=1 and B.ISSimulate=0");
            if (ReportType.Value == "P")
            {
                strSql.Append("  and A.ISReturn=0");
            }
            else
            {
                strSql.Append("  and A.ISReturn!=0");
            }

            if (TradeWBID.Value == "1")
            {
                strSql.Append("   AND A.WBID=H.WBID");
            }
            else if (TradeWBID.Value == "2")
            {
                strSql.Append("   AND A.WBID!=H.WBID");
            }

            if (Request.Form["QWBID"] != null && Request.Form["QWBID"].ToString() != "")
            {
                strSql.Append("   AND B.ID = " + Request.Form["QWBID"].ToString());
            }
            if (Qdtstart.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Sell> '" + Qdtstart.Value.Trim() + "'");
            }
            if (Qdtend.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Sell < '" + Qdtend.Value.Trim() + "'");
            }

            strSql.Append("    GROUP BY F.strName, B.strName,D.strName, E.strName,C.Price_ShiChang");
            strSql.Append("  ");

            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
            if (dt == null || dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                return SQLHelper.ExecuteDataTable(strSql.ToString());
            }
        }

        private void outputExcel()
        {
            DataTable dt = GetoutputTable();
            if (dt == null || dt.Rows.Count == 0)
            {
                Fun.Alert("请先检索要查询的信息后在导出！");
                return;
            }

            string fileName = "存转销报表" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + ".xls";

            HttpResponse resp;
            resp = Page.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            string colHeaders = "", ls_item = "", ls_Bottom = "";

            DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int i = 0;
            int cl = dt.Columns.Count;

            colHeaders += "储户网点\t交易网点\t存期\t存储产品\t交易量\t结算价\t利息\t保管费\t结算金额\n";
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息

            //逐行处理数据  
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据    
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加\n
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }
                resp.Write(ls_item);
                ls_item = "";

            }


            //添加第二张表格的内容

           
            DataTable dt2 = GetTotalReport();
            DataRow[] myRow2 = dt2.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的

            int cl2 = dt2.Columns.Count;

            colHeaders = "总计\n";
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息
            ls_item = "";
            //逐行处理数据  
            foreach (DataRow row in myRow2)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据    
                for (int j = 0; j < cl2; j++)
                {
                    if (j == (cl2 - 1))//最后一列，加\n
                    {
                        ls_item += row[j].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[j].ToString() + "\t";
                    }
                }
                resp.Write(ls_item);
                ls_item = "";

            }



            resp.End();
        }

        protected void btnOutPut_Click(object sender, EventArgs e)
        {
            outputExcel();
        }

    }
}